package com.demo;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ReadExcel {

    private List<String> headerList = new ArrayList<>();
    @Test
    public void testReadExcel() throws Exception{
        //创建输入流
//        InputStream fis = new FileInputStream(new File("E:\\Program Files (x86)\\656644393\\FileRecv\\限期改正2018年2月(3).xlsx"));
        FileInputStream fis = new FileInputStream(new File("E:\\xxx\\001.xlsx"));
        List<Map<String, Object>> lists = readExcel(fis);
        for (int i = 0; i < headerList.size(); i++) {
            System.out.println(headerList.get(i));
        }
        System.out.println("safdsa");
    }

    private List<Map<String, Object>> readExcel(InputStream fis) throws IOException {
        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        //由输入流得到工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        //得到工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        //读取表头行
        XSSFRow headerRow = getHeaderRow(sheet, 0);
        //读取数据
        XSSFFormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        for(int r = 1; r<= sheet.getLastRowNum();r++){
            XSSFRow dataRow = sheet.getRow(r);
            Map<String, Object> map = new HashMap<String, Object>();
            for(int h = 0; h<headerRow.getLastCellNum();h++){
                //表头为key
                String key = getHeaderCellValue(headerRow,h);
                headerList.add(key);
                //数据为value
                Object value = getCellValue(dataRow, h, evaluator);

                if(!key.equals("") && !key.equals("null") && key != null ){
                    map.put(key, value);
                }
            }
            lists.add(map);
        }
        workbook.close();
        fis.close();
        return lists;
    }
    private Object getCellValue(XSSFRow row,int cellIndex,FormulaEvaluator formula){
        XSSFCell cell = row.getCell(cellIndex);
        if(cell != null && cell.getCellType() != XSSFCell.CELL_TYPE_BLANK){
            switch (cell.getCellType()) {
                //String类型
                case XSSFCell.CELL_TYPE_STRING:
                    return cell.getRichStringCellValue().getString();
                //number类型
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else {
                        //return cell.getNumericCellValue();
                        DecimalFormat df = new DecimalFormat("0");
                        return df.format(cell.getNumericCellValue()).toString();
                        //return String.valueOf(cell.getNumericCellValue());
                    }
                //boolean类型
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                //公式
                case XSSFCell.CELL_TYPE_FORMULA:
                    return formula.evaluate(cell).getNumberValue();
                // 空值
                case HSSFCell.CELL_TYPE_BLANK:
                    return "";
                default:
                    return null;
            }
        }
        return null;
    }

    /**
     * 获取表头行
     * @Title: getHeaderRow
     * @param: @param sheet
     * @param: @param index
     * @param: @return
     * @return: Row
     * @throws
     */
    private XSSFRow getHeaderRow(XSSFSheet sheet, int index){
        XSSFRow headerRow = null;
        if(sheet!=null){
            headerRow = sheet.getRow(index);
        }
        return headerRow;
    }

    /**
     * 获取表头value
     * @Title: getHeaderCellValue
     * @param: @param headerRow
     * @param: @param cellIndex 英文表头所在的行，从0开始计算哦
     * @param: @param type  表头的类型第一种 姓名（name）英文于实体类或者数据库中的变量一致
     * @param: @return
     * @return: String
     * @throws
     */
    private String getHeaderCellValue(XSSFRow headerRow,int cellIndex){
        XSSFCell cell = headerRow.getCell(cellIndex);
        String headerValue = null;
        if(cell != null){
            headerValue = cell.getRichStringCellValue().getString();
        }
        return headerValue;
    }
}
